package model;

import java.sql.*;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

import db.DBUtil;
import db.DbException;
import forms.ApplicantForm;
import forms.ApplicantFormUpdate;

public class ApplicantModel {
	private static String selectStr = "SELECT id, firstName, middleInit, lastName FROM Applicant ORDER BY id";
	private static String selectUserStr = "SELECT * FROM Applicant, users WHERE userID = user_id AND user_name = ?";
	private static String insertStr = "INSERT INTO Applicant (first_name, middle_name, last_name) VALUES (?, ?, ?)";
	private static String updateStr = "UPDATE students SET first_name = ?, "
			+ "middle_name = ?, last_name = ? WHERE id = ?";

	public static CachedRowSet getAllApplicants() throws DbException {

		try {
			// Create the statement
			Statement stmt = DBUtil.createStatement();

			// Use the created statement to SELECT
			// the applicant attributes FROM the Applicant table.
			ResultSet allApplicants = stmt.executeQuery(selectStr);

			CachedRowSet crsApplicants = new CachedRowSetImpl();
			crsApplicants.populate(allApplicants);

			allApplicants.close();
			stmt.close();
			DBUtil.close();

			return crsApplicants;
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
	}

	public static void insertApplicant(ApplicantForm applicant)
			throws DbException {

		try {
			// Create the prepared statement and use it to
			// INSERT applicant values INTO the Applicant table.
			PreparedStatement pStmt = DBUtil.prepareStatement(insertStr);

			// pStmt.setString(1, applicant.getFirst());
			// pStmt.setString(2, applicant.getMiddle());
			// pStmt.setString(3, applicant.getLast());
			pStmt.executeUpdate();

			// Commit transaction
			// conn.commit();

			pStmt.close();
			DBUtil.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
	}

	public static void updateStudent(ApplicantFormUpdate student)
			throws DbException {

		try {
			// Create the prepared statement and use it to
			// UPDATE applicant values in the Applicant table.
			PreparedStatement pStmt = DBUtil.prepareStatement(updateStr);

			// pStmt.setInt(1, student.getStatus());
			pStmt.executeUpdate();

			// Commit transaction
			// conn.commit();

			pStmt.close();
			DBUtil.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
	}

	public static CachedRowSet getApplicantByUsername(String username)
			throws DbException {
		try {
			// Create the prepared statement and use it to
			// INSERT applicant values INTO the Applicant table.
			PreparedStatement pStmt = DBUtil.prepareStatement(selectUserStr);

			pStmt.setString(1, username);

			// Commit transaction
			// conn.commit();

			ResultSet applicant = pStmt.executeQuery();
			CachedRowSet crsApplicant = new CachedRowSetImpl();
			crsApplicant.populate(applicant);

			applicant.close();
			pStmt.close();
			DBUtil.close();

			if (crsApplicant.size() > 0)
				return crsApplicant;
			else
				return null;
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
	}

}
